from flask import Flask, render_template, request, redirect, url_for, session, flash, jsonify
from werkzeug.security import generate_password_hash, check_password_hash
from datetime import datetime, timedelta
import pymysql
import os
from functools import wraps
import base64
import json
from aip import AipFace
from config import config

app = Flask(__name__)
app.secret_key = 'your-secret-key-here'

# 文件上传配置
app.config['MAX_CONTENT_LENGTH'] = 50 * 1024 * 1024  # 50MB
app.config['UPLOAD_FOLDER'] = 'uploads'

# 加载配置
app.config.from_object(config['default'])

# MySQL数据库配置
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = '@HXYhxy050629'  # 修改为自己的MySQL密码
app.config['MYSQL_DB'] = 'campus_assistant'
app.config['MYSQL_CHARSET'] = 'utf8mb4'

# 初始化百度人脸识别客户端
client = AipFace(
    app.config['BAIDU_APP_ID'],
    app.config['BAIDU_API_KEY'],
    app.config['BAIDU_SECRET_KEY']
)

# 数据库连接函数
def get_db_connection():
    return pymysql.connect(
        host=app.config['MYSQL_HOST'],
        user=app.config['MYSQL_USER'],
        password=app.config['MYSQL_PASSWORD'],
        database=app.config['MYSQL_DB'],
        charset=app.config['MYSQL_CHARSET'],
        cursorclass=pymysql.cursors.DictCursor
    )

# 登录验证装饰器
def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated_function

# 管理员权限装饰器
def admin_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            return redirect(url_for('login'))
        
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute("SELECT is_admin FROM students WHERE id = %s", (session['user_id'],))
                user = cursor.fetchone()
                if not user or not user['is_admin']:
                    flash('需要管理员权限', 'error')
                    return redirect(url_for('dashboard'))
        finally:
            conn.close()
        return f(*args, **kwargs)
    return decorated_function

# 文件上传错误处理
@app.errorhandler(413)
def too_large(e):
    return jsonify({'success': False, 'error': '文件过大，请选择小于50MB的文件'}), 413

@app.route('/')
def index():
    return redirect(url_for('login'))

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute("SELECT id, password, name, is_admin FROM students WHERE username = %s", (username,))
                user = cursor.fetchone()
                
                if user and check_password_hash(user['password'], password):
                    # 在登录成功后
                    session['user_id'] = user['id']
                    session['student_id'] = user['id']  # 如果需要保持兼容性
                    session['username'] = username
                    session['name'] = user['name']
                    session['is_admin'] = user['is_admin']
                    session['role'] = 'admin' if user['is_admin'] else 'user'
                    flash(f'欢迎回来，{user["name"]}！', 'success')
                    return redirect(url_for('dashboard'))
                else:
                    flash('用户名或密码错误', 'error')
        finally:
            conn.close()
    
    return render_template('login.html')

@app.route('/logout')
def logout():
    session.clear()
    flash('已成功退出登录', 'info')
    return redirect(url_for('login'))

@app.route('/dashboard')
@login_required
def dashboard():
    return render_template('dashboard.html')

@app.route('/change_password', methods=['GET', 'POST'])
@login_required
def change_password():
    if request.method == 'POST':
        old_password = request.form['old_password']
        new_password = request.form['new_password']
        confirm_password = request.form['confirm_password']
        
        if new_password != confirm_password:
            flash('新密码两次输入不一致', 'error')
            return redirect(url_for('change_password'))
        
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute("SELECT password FROM students WHERE id = %s", (session['user_id'],))
                user = cursor.fetchone()
                
                if user and check_password_hash(user['password'], old_password):
                    new_password_hash = generate_password_hash(new_password)
                    cursor.execute("UPDATE students SET password = %s WHERE id = %s", 
                                 (new_password_hash, session['user_id']))
                    conn.commit()
                    flash('密码修改成功', 'success')
                    return redirect(url_for('dashboard'))
                else:
                    flash('原密码错误', 'error')
        finally:
            conn.close()
    
    return render_template('change_password.html')

@app.route('/schedule')
@login_required
def schedule():
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT course_name, classroom, teacher, time_slot 
                FROM courses WHERE student_id = %s
                ORDER BY time_slot
            """, (session['user_id'],))
            courses = cursor.fetchall()
            
            # 在Python中进行排序，按星期和时间排序
            def sort_key(course):
                time_slot = course['time_slot']
                # 提取星期
                weekday_map = {'周一': 1, '周二': 2, '周三': 3, '周四': 4, '周五': 5, '周六': 6, '周日': 7}
                weekday = 8  # 默认值
                for day, num in weekday_map.items():
                    if time_slot.startswith(day):
                        weekday = num
                        break
                
                # 提取时间部分
                time_part = time_slot.split(' ', 1)[1] if ' ' in time_slot else time_slot
                return (weekday, time_part)
            
            courses = sorted(courses, key=sort_key)
    finally:
        conn.close()
    
    return render_template('schedule.html', courses=courses)

@app.route('/add_course', methods=['POST'])
@login_required
def add_course():
    course_name = request.form['course_name']
    classroom = request.form['classroom']
    teacher = request.form['teacher']
    time_slot = request.form['time_slot']
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 检查时间冲突
            cursor.execute("""
                SELECT COUNT(*) as count FROM courses 
                WHERE student_id = %s AND time_slot = %s
            """, (session['user_id'], time_slot))
            
            if cursor.fetchone()['count'] > 0:
                flash('该时间段已有课程安排', 'error')
            else:
                cursor.execute("""
                    INSERT INTO courses (course_name, classroom, teacher, time_slot, student_id)
                    VALUES (%s, %s, %s, %s, %s)
                """, (course_name, classroom, teacher, time_slot, session['user_id']))
                conn.commit()
                flash('课程添加成功', 'success')
    finally:
        conn.close()
    
    return redirect(url_for('schedule'))


@app.route('/delete_course', methods=['POST'])
@login_required
def delete_course():
    data = request.get_json()
    course_name = data.get('course_name')
    time_slot = data.get('time_slot')
    
    if not course_name or not time_slot:
        return jsonify({'success': False, 'message': '参数不完整'})
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 删除课程
            cursor.execute("""
                DELETE FROM courses 
                WHERE student_id = %s AND course_name = %s AND time_slot = %s
            """, (session['user_id'], course_name, time_slot))
            
            if cursor.rowcount > 0:
                conn.commit()
                return jsonify({'success': True, 'message': '课程删除成功'})
            else:
                return jsonify({'success': False, 'message': '未找到要删除的课程'})
    except Exception as e:
        return jsonify({'success': False, 'message': f'删除失败: {str(e)}'})
    finally:
        conn.close()


@app.route('/lost_found')
@login_required
def lost_found():
    # 获取搜索参数
    search_item = request.args.get('search_item', '').strip()
    search_location = request.args.get('search_location', '').strip()
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 确保lost_items表存在
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS lost_items (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    item_name VARCHAR(200) NOT NULL,
                    description TEXT,
                    lost_location VARCHAR(200) NOT NULL,
                    lost_time DATETIME,
                    contact_info VARCHAR(200),
                    owner_id INT,
                    post_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    status VARCHAR(20) DEFAULT '寻找中',
                    FOREIGN KEY (owner_id) REFERENCES students(id) ON DELETE CASCADE
                )
            """)
            
            # 获取失物招领信息（找到的物品）
            found_query = """
                SELECT lf.id, lf.item_name, lf.location as location, s.name, lf.post_time, lf.finder_id, 'found' as type
                FROM lost_found lf
                JOIN students s ON lf.finder_id = s.id
                WHERE lf.status = '可领取'
            """
            
            # 获取丢失物品信息（丢失的物品）
            lost_query = """
                SELECT li.id, li.item_name, li.lost_location as location, s.name, li.post_time, li.owner_id as finder_id, 'lost' as type
                FROM lost_items li
                JOIN students s ON li.owner_id = s.id
                WHERE li.status = '寻找中'
            """
            
            # 添加搜索条件
            conditions = []
            params_found = []
            params_lost = []
            
            if search_item:
                conditions.append("item_name LIKE %s")
                params_found.append(f"%{search_item}%")
                params_lost.append(f"%{search_item}%")
            
            if search_location:
                found_conditions = conditions + ["lf.location LIKE %s"]
                lost_conditions = conditions + ["li.lost_location LIKE %s"]
                params_found.append(f"%{search_location}%")
                params_lost.append(f"%{search_location}%")
            else:
                found_conditions = conditions
                lost_conditions = conditions
            
            # 组合查询条件
            if found_conditions:
                found_query += " AND " + " AND ".join([c.replace("item_name", "lf.item_name") for c in found_conditions])
            if lost_conditions:
                lost_query += " AND " + " AND ".join([c.replace("item_name", "li.item_name") for c in lost_conditions])
            
            # 执行查询
            cursor.execute(found_query, params_found)
            found_items = cursor.fetchall()
            
            cursor.execute(lost_query, params_lost)
            lost_items = cursor.fetchall()
            
            # 合并结果并按时间排序
            all_items = list(found_items) + list(lost_items)
            all_items.sort(key=lambda x: x['post_time'], reverse=True)
            
    finally:
        conn.close()
    
    return render_template('lost_found.html', items=all_items)

@app.route('/post_lost_item', methods=['POST'])
@login_required
def post_lost_item():
    item_name = request.form['item_name']
    location = request.form['location']
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                INSERT INTO lost_found (item_name, location, finder_id)
                VALUES (%s, %s, %s)
            """, (item_name, location, session['user_id']))
            conn.commit()
            flash('失物信息发布成功', 'success')
    finally:
        conn.close()
    
    return redirect(url_for('lost_found'))

@app.route('/send_message', methods=['POST'])
@login_required
def send_message():
    data = request.get_json()
    item_id = data.get('item_id')
    receiver_id = data.get('receiver_id')
    message = data.get('message')
    item_type = data.get('item_type', 'found')  # 默认为失物招领
    
    if not all([item_id, receiver_id, message]):
        return jsonify({'success': False, 'error': '缺少必要参数'})
    
    # 验证物品是否存在
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            if item_type == 'lost':
                cursor.execute("SELECT id FROM lost_items WHERE id = %s", (item_id,))
            else:
                cursor.execute("SELECT id FROM lost_found WHERE id = %s", (item_id,))
            
            if not cursor.fetchone():
                return jsonify({'success': False, 'error': '物品信息不存在'})
            
            # 插入消息
            cursor.execute("""
                INSERT INTO lost_found_messages (lost_item_id, sender_id, receiver_id, message, item_type)
                VALUES (%s, %s, %s, %s, %s)
            """, (item_id, session['user_id'], receiver_id, message, item_type))
            conn.commit()
            
            return jsonify({'success': True})
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
    finally:
        conn.close()

@app.route('/get_messages/<int:item_id>/<item_type>')
@login_required
def get_messages(item_id, item_type):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 确保消息表包含item_type字段
            cursor.execute("""
                SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = 'lost_found_messages' AND COLUMN_NAME = 'item_type'
            """)
            if not cursor.fetchone():
                cursor.execute("""
                    ALTER TABLE lost_found_messages 
                    ADD COLUMN item_type VARCHAR(10) DEFAULT 'found'
                """)
                # 更新现有记录的item_type为'found'
                cursor.execute("""
                    UPDATE lost_found_messages 
                    SET item_type = 'found' 
                    WHERE item_type IS NULL
                """)
                conn.commit()
            
            # 获取与该物品相关的所有消息
            cursor.execute("""
                SELECT m.id, m.sender_id, m.receiver_id, m.message, m.sent_time, s.name as sender_name
                FROM lost_found_messages m
                JOIN students s ON m.sender_id = s.id
                WHERE m.lost_item_id = %s AND m.item_type = %s
                AND (m.sender_id = %s OR m.receiver_id = %s)
                ORDER BY m.sent_time ASC
            """, (item_id, item_type, session['user_id'], session['user_id']))
            messages = cursor.fetchall()
            
            # 标记消息为已读
            cursor.execute("""
                UPDATE lost_found_messages 
                SET is_read = 1 
                WHERE lost_item_id = %s AND item_type = %s AND receiver_id = %s
            """, (item_id, item_type, session['user_id']))
            conn.commit()
            
            return jsonify({'success': True, 'messages': messages})
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
    finally:
        conn.close()

@app.route('/my_messages')
@login_required
def my_messages():
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 确保lost_found_messages表包含item_type字段
            cursor.execute("""
                SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = 'lost_found_messages' AND COLUMN_NAME = 'item_type'
            """)
            if not cursor.fetchone():
                cursor.execute("""
                    ALTER TABLE lost_found_messages 
                    ADD COLUMN item_type VARCHAR(10) DEFAULT 'found'
                """)
                # 更新现有记录的item_type为'found'
                cursor.execute("""
                    UPDATE lost_found_messages 
                    SET item_type = 'found' 
                    WHERE item_type IS NULL
                """)
                conn.commit()
            
            conversations = []
            
            # 获取失物招领的对话
            cursor.execute("""
                SELECT DISTINCT
                    lf.id as item_id,
                    lf.item_name,
                    'found' as item_type,
                    CASE 
                        WHEN lf.finder_id = %s THEN 
                            (SELECT DISTINCT sender_id FROM lost_found_messages 
                             WHERE lost_item_id = lf.id AND item_type = 'found' AND sender_id != %s LIMIT 1)
                        ELSE COALESCE(lf.finder_id, 
                            (SELECT DISTINCT sender_id FROM lost_found_messages 
                             WHERE lost_item_id = lf.id AND item_type = 'found' AND sender_id != %s LIMIT 1)
                        )
                    END as contact_id,
                    CASE 
                        WHEN lf.finder_id = %s THEN 
                            (SELECT s.name FROM students s 
                             JOIN lost_found_messages m ON s.id = m.sender_id 
                             WHERE m.lost_item_id = lf.id AND m.item_type = 'found' AND m.sender_id != %s LIMIT 1)
                        ELSE COALESCE(
                            (SELECT name FROM students WHERE id = lf.finder_id),
                            (SELECT s.name FROM students s 
                             JOIN lost_found_messages m ON s.id = m.sender_id 
                             WHERE m.lost_item_id = lf.id AND m.item_type = 'found' AND m.sender_id != %s LIMIT 1)
                        )
                    END as contact_name,
                    (SELECT message FROM lost_found_messages 
                     WHERE lost_item_id = lf.id AND item_type = 'found'
                     ORDER BY sent_time DESC LIMIT 1) as last_message,
                    (SELECT sent_time FROM lost_found_messages 
                     WHERE lost_item_id = lf.id AND item_type = 'found'
                     ORDER BY sent_time DESC LIMIT 1) as last_time,
                    (
                        SELECT COUNT(*) 
                        FROM lost_found_messages m2 
                        WHERE m2.lost_item_id = lf.id AND m2.item_type = 'found'
                        AND m2.receiver_id = %s 
                        AND m2.is_read = 0
                    ) as unread_count
                FROM lost_found lf
                WHERE lf.id IN (
                    SELECT DISTINCT lost_item_id 
                    FROM lost_found_messages 
                    WHERE (sender_id = %s OR receiver_id = %s) AND item_type = 'found'
                )
                ORDER BY last_time DESC
            """, (session['user_id'], session['user_id'], session['user_id'], session['user_id'], session['user_id'], session['user_id'], session['user_id'], session['user_id'], session['user_id']))
            
            found_conversations = cursor.fetchall()
            conversations.extend(found_conversations)
            
            # 获取丢失物品的对话
            cursor.execute("""
                SELECT DISTINCT
                    li.id as item_id,
                    li.item_name,
                    'lost' as item_type,
                    CASE 
                        WHEN li.owner_id = %s THEN 
                            (SELECT DISTINCT sender_id FROM lost_found_messages 
                             WHERE lost_item_id = li.id AND item_type = 'lost' AND sender_id != %s LIMIT 1)
                        ELSE COALESCE(li.owner_id, 
                            (SELECT DISTINCT sender_id FROM lost_found_messages 
                             WHERE lost_item_id = li.id AND item_type = 'lost' AND sender_id != %s LIMIT 1)
                        )
                    END as contact_id,
                    CASE 
                        WHEN li.owner_id = %s THEN 
                            (SELECT s.name FROM students s 
                             JOIN lost_found_messages m ON s.id = m.sender_id 
                             WHERE m.lost_item_id = li.id AND m.item_type = 'lost' AND m.sender_id != %s LIMIT 1)
                        ELSE COALESCE(
                            (SELECT name FROM students WHERE id = li.owner_id),
                            (SELECT s.name FROM students s 
                             JOIN lost_found_messages m ON s.id = m.sender_id 
                             WHERE m.lost_item_id = li.id AND m.item_type = 'lost' AND m.sender_id != %s LIMIT 1)
                        )
                    END as contact_name,
                    (SELECT message FROM lost_found_messages 
                     WHERE lost_item_id = li.id AND item_type = 'lost'
                     ORDER BY sent_time DESC LIMIT 1) as last_message,
                    (SELECT sent_time FROM lost_found_messages 
                     WHERE lost_item_id = li.id AND item_type = 'lost'
                     ORDER BY sent_time DESC LIMIT 1) as last_time,
                    (
                        SELECT COUNT(*) 
                        FROM lost_found_messages m2 
                        WHERE m2.lost_item_id = li.id AND m2.item_type = 'lost'
                        AND m2.receiver_id = %s 
                        AND m2.is_read = 0
                    ) as unread_count
                FROM lost_items li
                WHERE li.id IN (
                    SELECT DISTINCT lost_item_id 
                    FROM lost_found_messages 
                    WHERE (sender_id = %s OR receiver_id = %s) AND item_type = 'lost'
                )
                ORDER BY last_time DESC
            """, (session['user_id'], session['user_id'], session['user_id'], session['user_id'], session['user_id'], session['user_id'], session['user_id'], session['user_id'], session['user_id']))
            
            lost_conversations = cursor.fetchall()
            conversations.extend(lost_conversations)
            
            # 按最后消息时间排序
            conversations.sort(key=lambda x: x['last_time'] if x['last_time'] else datetime.min, reverse=True)
            
            return render_template('my_messages.html', conversations=conversations)
    except Exception as e:
        flash(f'获取消息列表失败：{str(e)}', 'error')
        return render_template('my_messages.html', conversations=[])
    finally:
        conn.close()

@app.route('/delete_lost_item/<int:item_id>')
@login_required
def delete_lost_item(item_id):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                DELETE FROM lost_found 
                WHERE id = %s AND finder_id = %s
            """, (item_id, session['user_id']))
            conn.commit()
            flash('失物信息删除成功', 'success')
    finally:
        conn.close()
    
    return redirect(url_for('lost_found'))

# 丢失物品相关路由
@app.route('/post_lost_item_request', methods=['POST'])
@login_required
def post_lost_item_request():
    """发布丢失物品信息"""
    item_name = request.form['item_name']
    description = request.form.get('description', '')
    lost_location = request.form['lost_location']
    lost_time = request.form.get('lost_time')
    contact_info = request.form.get('contact_info', '')
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 检查lost_items表是否存在，如果不存在则创建
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS lost_items (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    item_name VARCHAR(200) NOT NULL,
                    description TEXT,
                    lost_location VARCHAR(200) NOT NULL,
                    lost_time DATETIME,
                    contact_info VARCHAR(200),
                    owner_id INT,
                    post_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    status VARCHAR(20) DEFAULT '寻找中',
                    FOREIGN KEY (owner_id) REFERENCES students(id) ON DELETE CASCADE
                )
            """)
            
            # 插入丢失物品信息
            cursor.execute("""
                INSERT INTO lost_items (item_name, description, lost_location, lost_time, contact_info, owner_id)
                VALUES (%s, %s, %s, %s, %s, %s)
            """, (item_name, description, lost_location, lost_time, contact_info, session['user_id']))
            conn.commit()
            flash('丢失物品信息发布成功', 'success')
    except Exception as e:
        flash(f'发布失败：{str(e)}', 'error')
    finally:
        conn.close()
    
    return redirect(url_for('lost_found'))

@app.route('/delete_lost_item_request/<int:item_id>')
@login_required
def delete_lost_item_request(item_id):
    """删除丢失物品信息"""
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                DELETE FROM lost_items 
                WHERE id = %s AND owner_id = %s
            """, (item_id, session['user_id']))
            conn.commit()
            flash('丢失物品信息删除成功', 'success')
    except Exception as e:
        flash(f'删除失败：{str(e)}', 'error')
    finally:
        conn.close()
    
    return redirect(url_for('lost_found'))

@app.route('/library')
@login_required
def library():
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 获取查询参数
            query_date = request.args.get('date')
            start_time = request.args.get('start_time')
            end_time = request.args.get('end_time')
            
            # 如果没有指定日期，使用今天
            if not query_date:
                query_date = datetime.now().date()
            else:
                query_date = datetime.strptime(query_date, '%Y-%m-%d').date()
            
            # 如果没有指定时间，使用当前时间
            if not start_time or not end_time:
                current_time = datetime.now().time()
                time_condition = "AND sr.start_time_slot <= %s AND sr.end_time_slot > %s"
                time_params = [current_time.strftime('%H:%M'), current_time.strftime('%H:%M')]
            else:
                time_condition = "AND ((sr.start_time_slot < %s AND sr.end_time_slot > %s) OR (sr.start_time_slot < %s AND sr.end_time_slot > %s) OR (sr.start_time_slot >= %s AND sr.end_time_slot <= %s))"
                time_params = [end_time, start_time, start_time, end_time, start_time, end_time]
            
            # 获取座位信息，包含指定时间的预约状态
            query = """
                SELECT ls.id, ls.floor, ls.seat_number, ls.status,
                       sr.student_id, s.name, sr.start_time_slot, sr.end_time_slot,
                       sr.status as reservation_status
                FROM library_seats ls
                LEFT JOIN seat_reservations sr ON ls.id = sr.seat_id 
                    AND sr.reservation_date = %s
                    AND sr.status IN ('已预约', '使用中', 'active', 'completed')
                LEFT JOIN students s ON sr.student_id = s.id
                ORDER BY ls.floor, ls.seat_number
            """
            
            cursor.execute(query, [query_date])
            seats = cursor.fetchall()
            
            # 获取在线人数
            online_query = """
                SELECT COUNT(*) as count FROM seat_reservations 
                WHERE reservation_date = %s AND status IN ('已预约', '使用中', 'active', 'completed')
            """
            
            cursor.execute(online_query, [query_date])
            online_count = cursor.fetchone()['count']
            
    finally:
        conn.close()
    
    return render_template('library.html', seats=seats, online_count=online_count)

@app.route('/reserve_seat', methods=['POST'])
@login_required
def reserve_seat():
    data = request.get_json()
    seat_id = data.get('seat_id')
    date = data.get('date')
    start_time = data.get('start_time')
    end_time = data.get('end_time')
    
    if not all([seat_id, date, start_time, end_time]):
        return jsonify({'success': False, 'message': '缺少必要参数'})
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 检查用户是否已有当天的预约
            cursor.execute("""
                SELECT COUNT(*) as count FROM seat_reservations 
                WHERE student_id = %s AND reservation_date = %s AND status = '已预约'
            """, (session['user_id'], date))
            
            if cursor.fetchone()['count'] > 0:
                return jsonify({'success': False, 'message': '您当天已有预约，请先取消当前预约'})
            
            # 检查座位在指定时间段是否可用
            cursor.execute("""
                SELECT COUNT(*) as count FROM seat_reservations 
                WHERE seat_id = %s AND reservation_date = %s 
                AND status IN ('已预约', '使用中')
                AND (
                    (start_time_slot < %s AND end_time_slot > %s) OR
                    (start_time_slot < %s AND end_time_slot > %s) OR
                    (start_time_slot >= %s AND end_time_slot <= %s)
                )
            """, (seat_id, date, end_time, start_time, start_time, end_time, start_time, end_time))
            
            if cursor.fetchone()['count'] > 0:
                return jsonify({'success': False, 'message': '该时间段座位已被预约'})
            
            # 创建预约
            cursor.execute("""
                INSERT INTO seat_reservations 
                (student_id, seat_id, reservation_date, start_time_slot, end_time_slot, status, created_at)
                VALUES (%s, %s, %s, %s, %s, '已预约', NOW())
            """, (session['user_id'], seat_id, date, start_time, end_time))
            
            # 更新座位状态
            cursor.execute("""
                UPDATE library_seats SET status = 'reserved' WHERE id = %s
            """, (seat_id,))
            
            conn.commit()
            return jsonify({'success': True, 'message': '预约成功'})
            
    except Exception as e:
        conn.rollback()
        return jsonify({'success': False, 'message': f'预约失败: {str(e)}'})
    finally:
        conn.close()

@app.route('/reserve_seat/<int:seat_id>')
@login_required
def reserve_seat_old(seat_id):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 检查用户是否已有预约
            cursor.execute("""
                SELECT COUNT(*) as count FROM seat_reservations 
                WHERE student_id = %s AND status = 'active'
            """, (session['user_id'],))
            
            if cursor.fetchone()['count'] > 0:
                flash('您已有座位预约，请先取消当前预约', 'error')
            else:
                # 检查座位是否可用
                cursor.execute("""
                    SELECT COUNT(*) as count FROM seat_reservations 
                    WHERE seat_id = %s AND status = 'active'
                """, (seat_id,))
                
                if cursor.fetchone()['count'] > 0:
                    flash('该座位已被预约', 'error')
                else:
                    start_time = datetime.now()
                    cursor.execute("""
                        INSERT INTO seat_reservations (student_id, seat_id, start_time, status)
                        VALUES (%s, %s, %s, 'active')
                    """, (session['user_id'], seat_id, start_time))
                    conn.commit()
                    flash('座位预约成功', 'success')
    finally:
        conn.close()
    
    return redirect(url_for('library'))

@app.route('/cancel_reservation/<int:seat_id>')
@login_required
def cancel_reservation(seat_id):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                DELETE FROM seat_reservations 
                WHERE seat_id = %s AND student_id = %s AND status = '已预约'
            """, (seat_id, session['user_id']))
            conn.commit()
            flash('预约已取消', 'success')
    finally:
        conn.close()
    
    return redirect(url_for('library'))

@app.route('/reserve_seat_with_time', methods=['POST'])
@login_required
def reserve_seat_with_time():
    data = request.get_json()
    seat_id = data.get('seat_id')
    date = data.get('date')
    start_time = data.get('start_time')
    end_time = data.get('end_time')
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 检查用户是否已有当天的预约
            cursor.execute("""
                SELECT COUNT(*) as count FROM seat_reservations 
                WHERE student_id = %s AND reservation_date = %s AND status = '已预约'
            """, (session['user_id'], date))
            
            if cursor.fetchone()['count'] > 0:
                return jsonify({'success': False, 'message': '您当天已有预约，请先取消当前预约'})
            
            # 检查座位在指定时间段是否可用
            cursor.execute("""
                SELECT COUNT(*) as count FROM seat_reservations 
                WHERE seat_id = %s AND reservation_date = %s 
                AND status IN ('已预约', '使用中')
                AND (
                    (start_time_slot < %s AND end_time_slot > %s) OR
                    (start_time_slot < %s AND end_time_slot > %s) OR
                    (start_time_slot >= %s AND end_time_slot <= %s)
                )
            """, (seat_id, date, end_time, start_time, start_time, end_time, start_time, end_time))
            
            if cursor.fetchone()['count'] > 0:
                return jsonify({'success': False, 'message': '该时间段座位已被预约'})
            
            # 创建预约
            cursor.execute("""
                INSERT INTO seat_reservations 
                (student_id, seat_id, reservation_date, start_time_slot, end_time_slot, status)
                VALUES (%s, %s, %s, %s, %s, '已预约')
            """, (session['user_id'], seat_id, date, start_time, end_time))
            conn.commit()
            
            return jsonify({'success': True, 'message': '预约成功'})
    except Exception as e:
        conn.rollback()
        return jsonify({'success': False, 'message': '预约失败，请重试'})
    finally:
        conn.close()

# 座位管理页面
@app.route('/seat_management/<int:seat_id>')
@login_required
def seat_management(seat_id):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 获取座位信息
            cursor.execute("""
                SELECT id, floor, seat_number, status
                FROM library_seats
                WHERE id = %s
            """, (seat_id,))
            seat = cursor.fetchone()
            
            if not seat:
                flash('座位不存在', 'error')
                return redirect(url_for('library'))
                
    finally:
        conn.close()
    
    return render_template('seat_management.html', seat=seat)

# 管理员座位管理页面
@app.route('/admin_seat_management')
@admin_required
def admin_seat_management():
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 获取今日座位信息
            today = datetime.now().date()
            cursor.execute("""
                SELECT ls.id, ls.floor, ls.seat_number, ls.status,
                       s.name, s.student_id,
                       sr.start_time_slot, sr.end_time_slot, sr.reservation_date, sr.status as reservation_status
                FROM library_seats ls
                LEFT JOIN seat_reservations sr ON ls.id = sr.seat_id 
                    AND sr.reservation_date = %s
                LEFT JOIN students s ON sr.student_id = s.id
                ORDER BY ls.floor, ls.seat_number
            """, (today,))
            seats = cursor.fetchall()
            
            # 获取所有座位总数
            cursor.execute("SELECT COUNT(*) as total FROM library_seats")
            total_count = cursor.fetchone()['total']
            
            # 统计今日各状态座位数量
            cursor.execute("""
                SELECT 
                    COUNT(CASE WHEN (sr.status = 'completed' AND sr.actual_start_time IS NOT NULL AND sr.actual_end_time IS NULL) OR sr.status = '使用中' THEN 1 END) as occupied_count,
                    COUNT(CASE WHEN sr.status = 'active' OR sr.status = '已预约' THEN 1 END) as reserved_count,
                    COUNT(CASE WHEN sr.status = 'completed' AND sr.actual_end_time IS NOT NULL THEN 1 END) as completed_count
                FROM library_seats ls
                LEFT JOIN seat_reservations sr ON ls.id = sr.seat_id 
                    AND sr.reservation_date = %s
            """, (today,))
            counts = cursor.fetchone()
            
            occupied_count = counts['occupied_count'] or 0
            reserved_count = counts['reserved_count'] or 0
            completed_count = counts['completed_count'] or 0
            available_count = total_count - occupied_count - reserved_count - completed_count
                
    finally:
        conn.close()
    
    return render_template('admin_seat_management.html', 
                         seats=seats,
                         available_count=available_count,
                         reserved_count=reserved_count,
                         occupied_count=occupied_count,
                         total_count=total_count)

# 查询座位预约记录
@app.route('/query_seat_reservations', methods=['POST'])
@login_required
def query_seat_reservations():
    data = request.get_json()
    seat_id = data.get('seat_id')
    date = data.get('date')
    start_time = data.get('start_time')
    end_time = data.get('end_time')
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 查询指定时间段内的预约记录
            cursor.execute("""
                SELECT sr.id, sr.student_id, sr.reservation_date, 
                       sr.start_time_slot, sr.end_time_slot, sr.status,
                       s.name as student_name
                FROM seat_reservations sr
                JOIN students s ON sr.student_id = s.id
                WHERE sr.seat_id = %s AND sr.reservation_date = %s
                AND (
                    (sr.start_time_slot < %s AND sr.end_time_slot > %s) OR
                    (sr.start_time_slot < %s AND sr.end_time_slot > %s) OR
                    (sr.start_time_slot >= %s AND sr.end_time_slot <= %s)
                )
                ORDER BY sr.start_time_slot
            """, (seat_id, date, end_time, start_time, start_time, end_time, start_time, end_time))
            
            reservations = cursor.fetchall()
            
            return jsonify({
                'success': True,
                'reservations': reservations
            })
            
    except Exception as e:
        return jsonify({'success': False, 'message': '查询失败，请重试'})
    finally:
        conn.close()

# 管理员创建预约
@app.route('/admin_create_reservation', methods=['POST'])
@login_required
def admin_create_reservation():
    data = request.get_json()
    student_id = data.get('student_id')
    seat_id = data.get('seat_id')
    date = data.get('date')
    start_time = data.get('start_time')
    end_time = data.get('end_time')
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 检查学生是否存在
            cursor.execute("SELECT id FROM students WHERE id = %s", (student_id,))
            if not cursor.fetchone():
                return jsonify({'success': False, 'message': '学生ID不存在'})
            
            # 检查座位在指定时间段是否可用
            cursor.execute("""
                SELECT COUNT(*) as count FROM seat_reservations 
                WHERE seat_id = %s AND reservation_date = %s 
                AND status IN ('已预约', '使用中')
                AND (
                    (start_time_slot < %s AND end_time_slot > %s) OR
                    (start_time_slot < %s AND end_time_slot > %s) OR
                    (start_time_slot >= %s AND end_time_slot <= %s)
                )
            """, (seat_id, date, end_time, start_time, start_time, end_time, start_time, end_time))
            
            if cursor.fetchone()['count'] > 0:
                return jsonify({'success': False, 'message': '该时间段座位已被预约'})
            
            # 创建预约
            cursor.execute("""
                INSERT INTO seat_reservations 
                (student_id, seat_id, reservation_date, start_time_slot, end_time_slot, status)
                VALUES (%s, %s, %s, %s, %s, '已预约')
            """, (student_id, seat_id, date, start_time, end_time))
            conn.commit()
            
            return jsonify({'success': True, 'message': '预约创建成功'})
            
    except Exception as e:
        conn.rollback()
        return jsonify({'success': False, 'message': '创建失败，请重试'})
    finally:
        conn.close()

# 管理员修改预约状态
@app.route('/admin_change_reservation_status', methods=['POST'])
@login_required
def admin_change_reservation_status():
    data = request.get_json()
    reservation_id = data.get('reservation_id')
    new_status = data.get('new_status')
    
    if new_status not in ['active', 'completed', 'cancelled', 'expired']:
        return jsonify({'success': False, 'message': '无效的状态'})
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 更新预约状态
            cursor.execute("""
                UPDATE seat_reservations 
                SET status = %s
                WHERE id = %s
            """, (new_status, reservation_id))
            
            if cursor.rowcount == 0:
                return jsonify({'success': False, 'message': '预约记录不存在'})
            
            conn.commit()
            return jsonify({'success': True, 'message': '状态修改成功'})
            
    except Exception as e:
        conn.rollback()
        return jsonify({'success': False, 'message': '修改失败，请重试'})
    finally:
        conn.close()

# 管理员删除预约
@app.route('/admin_delete_reservation', methods=['POST'])
@login_required
def admin_delete_reservation():
    data = request.get_json()
    reservation_id = data.get('reservation_id')
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 删除预约记录
            cursor.execute("""
                DELETE FROM seat_reservations 
                WHERE id = %s
            """, (reservation_id,))
            
            if cursor.rowcount == 0:
                return jsonify({'success': False, 'message': '预约记录不存在'})
            
            conn.commit()
            return jsonify({'success': True, 'message': '预约删除成功'})
            
    except Exception as e:
        conn.rollback()
        return jsonify({'success': False, 'message': '删除失败，请重试'})
    finally:
        conn.close()


@app.route('/cancel_reservation/<int:seat_id>', methods=['POST'])
@login_required
def cancel_reservation_new(seat_id):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                DELETE FROM seat_reservations 
                WHERE seat_id = %s AND student_id = %s AND status = '已预约'
            """, (seat_id, session['user_id']))
            conn.commit()
            
            if cursor.rowcount > 0:
                return jsonify({'success': True, 'message': '预约已取消'})
            else:
                return jsonify({'success': False, 'message': '未找到可取消的预约'})
    except Exception as e:
        conn.rollback()
        return jsonify({'success': False, 'message': '取消失败，请重试'})
    finally:
        conn.close()

@app.route('/start_using_seat/<int:seat_id>', methods=['POST'])
@login_required
def start_using_seat(seat_id):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 将用户的预约状态改为使用中
            cursor.execute("""
                UPDATE seat_reservations 
                SET status = '使用中', actual_start_time = %s
                WHERE seat_id = %s AND student_id = %s AND status = '已预约'
            """, (datetime.now(), seat_id, session['user_id']))
            conn.commit()
            
            if cursor.rowcount > 0:
                return jsonify({'success': True, 'message': '开始使用座位'})
            else:
                return jsonify({'success': False, 'message': '未找到可用的预约'})
    except Exception as e:
        conn.rollback()
        return jsonify({'success': False, 'message': '操作失败，请重试'})
    finally:
        conn.close()

@app.route('/end_using_seat/<int:seat_id>', methods=['POST'])
@login_required
def end_using_seat(seat_id):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 结束使用，删除预约记录
            cursor.execute("""
                UPDATE seat_reservations 
                SET actual_end_time = %s
                WHERE seat_id = %s AND student_id = %s AND status = '使用中'
            """, (datetime.now(), seat_id, session['user_id']))
            
            # 或者直接删除记录
            cursor.execute("""
                DELETE FROM seat_reservations 
                WHERE seat_id = %s AND student_id = %s AND status = '使用中'
            """, (seat_id, session['user_id']))
            
            conn.commit()
            
            if cursor.rowcount > 0:
                return jsonify({'success': True, 'message': '结束使用座位'})
            else:
                return jsonify({'success': False, 'message': '未找到使用中的座位'})
    except Exception as e:
        conn.rollback()
        return jsonify({'success': False, 'message': '操作失败，请重试'})
    finally:
        conn.close()

@app.route('/admin')
@admin_required
def admin():
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 获取用户统计
            cursor.execute("SELECT COUNT(*) as count FROM students WHERE is_admin = 0")
            student_count = cursor.fetchone()['count']
            
            cursor.execute("SELECT COUNT(*) as count FROM seat_reservations WHERE status = '已预约'")
            reservation_count = cursor.fetchone()['count']
            
            cursor.execute("SELECT COUNT(*) as count FROM lost_found WHERE status = '可领取'")
            lost_item_count = cursor.fetchone()['count']
            
            # 获取所有学生
            cursor.execute("""
                SELECT id, username, name, major, class_name, student_id
                FROM students WHERE is_admin = 0
                ORDER BY student_id
            """)
            students = cursor.fetchall()
    finally:
        conn.close()
    
    return render_template('admin.html', 
                         student_count=student_count,
                         reservation_count=reservation_count,
                         lost_item_count=lost_item_count,
                         students=students)

@app.route('/admin/add_news', methods=['POST'])
@admin_required
def add_news():
    title = request.form['title']
    content = request.form['content']
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                INSERT INTO news (title, content, publisher_id)
                VALUES (%s, %s, %s)
            """, (title, content, session['user_id']))
            conn.commit()
            flash('新闻发布成功', 'success')
    finally:
        conn.close()
    
    return redirect(url_for('news'))

@app.route('/admin/add_student', methods=['POST'])
@admin_required
def add_student():
    username = request.form['username']
    name = request.form['name']
    major = request.form['major']
    class_name = request.form['class_name']
    student_id = request.form['student_id']
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            try:
                password_hash = generate_password_hash('123456')  # 默认密码
                cursor.execute("""
                    INSERT INTO students (username, password, name, major, class_name, student_id, is_admin)
                    VALUES (%s, %s, %s, %s, %s, %s, 0)
                """, (username, password_hash, name, major, class_name, student_id))
                conn.commit()
                flash('学生添加成功，默认密码为123456', 'success')
            except pymysql.IntegrityError:
                flash('用户名或学号已存在', 'error')
    finally:
        conn.close()
    
    return redirect(url_for('admin'))

@app.route('/news')
@login_required
def news():
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT n.id, n.title, n.content, n.publish_time, s.name
                FROM news n
                JOIN students s ON n.publisher_id = s.id
                ORDER BY n.publish_time DESC
            """)
            news_list = cursor.fetchall()
    finally:
        conn.close()
    
    return render_template('news.html', news_list=news_list)

# 人脸识别相关路由
@app.route('/face_recognition/library_entry', methods=['POST'])
@login_required
def face_recognition_library_entry():
    """图书馆人脸识别进入"""
    try:
        data = request.get_json()
        face_image = data.get('face_image')  # base64编码的图片
        
        if not face_image:
            return jsonify({'success': False, 'message': '请提供人脸图片'})
        
        # 移除base64前缀
        if 'data:image' in face_image:
            face_image = face_image.split(',')[1]
        
        # 人脸检测
        face_detect_result = client.detect(face_image, 'BASE64')
        
        if face_detect_result['error_code'] != 0:
            return jsonify({'success': False, 'message': '人脸检测失败，请重新拍照'})
        
        if len(face_detect_result['result']['face_list']) == 0:
            return jsonify({'success': False, 'message': '未检测到人脸，请重新拍照'})
        
        # 获取当前用户的注册照片进行比对
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute("""
                    SELECT name, profile_photo FROM students 
                    WHERE id = %s AND profile_photo IS NOT NULL
                """, (session['user_id'],))
                user = cursor.fetchone()
                
                if not user or not user['profile_photo']:
                    return jsonify({
                        'success': False, 
                        'message': '请先在个人中心上传头像照片进行实名认证'
                    })
                
                # 人脸比对
                match_result = client.match([
                    {'image': face_image, 'image_type': 'BASE64'},
                    {'image': user['profile_photo'], 'image_type': 'BASE64'}
                ])
                
                if match_result['error_code'] != 0:
                    return jsonify({'success': False, 'message': '人脸比对失败，请重试'})
                
                # 获取相似度分数
                score = match_result['result']['score']
                
                # 设置阈值（通常80以上认为是同一人）
                if score >= 80:
                    # 记录进入日志
                    cursor.execute("""
                        INSERT INTO library_entry_log (student_id, entry_time, recognition_score)
                        VALUES (%s, %s, %s)
                    """, (session['user_id'], datetime.now(), score))
                    conn.commit()
                    
                    return jsonify({
                        'success': True, 
                        'message': f'您好，欢迎{user["name"]}进入图书馆！',
                        'score': score
                    })
                else:
                    return jsonify({
                        'success': False, 
                        'message': f'人脸识别失败，相似度过低({score:.1f}%)，请重试',
                        'score': score
                    })
                    
        finally:
            conn.close()
            
    except Exception as e:
        return jsonify({'success': False, 'message': f'系统错误：{str(e)}'})

@app.route('/face_recognition/identity_verify', methods=['POST'])
@login_required
def face_recognition_identity_verify():
    """个人中心实名认证 - 简化版本，只需检测人脸"""
    try:
        data = request.get_json()
        image_data = data.get('image')  # base64编码的图片
        
        if not image_data:
            return jsonify({'success': False, 'message': '请提供照片'})
        
        # 移除base64前缀
        if 'data:image' in image_data:
            image_data = image_data.split(',')[1]
        
        # 人脸检测
        face_detect_result = client.detect(image_data, 'BASE64')
        
        if face_detect_result['error_code'] != 0:
            return jsonify({'success': False, 'message': '照片处理失败，请重新上传'})
        
        face_list = face_detect_result['result']['face_list']
        if len(face_list) == 0:
            return jsonify({'success': False, 'message': '照片中未检测到人脸，请上传包含清晰人脸的照片'})
        
        if len(face_list) > 1:
            return jsonify({'success': False, 'message': '检测到多张人脸，请上传只包含一人的照片'})
        
        # 简化的人脸质量检查（可选）
        face_info = face_list[0]
        
        # 检查是否有quality字段，如果没有则跳过质量检查
        if 'quality' in face_info:
            face_quality = face_info['quality']
            if 'completeness' in face_quality and face_quality['completeness'] < 0.6:
                return jsonify({'success': False, 'message': '人脸不够完整，请上传更清晰的照片'})
        
        # 保存照片并标记为已认证
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute("""
                    UPDATE students 
                    SET profile_photo = %s, is_verified = 1, verify_time = NOW() 
                    WHERE id = %s
                """, (image_data, session['user_id']))
                conn.commit()
                
                return jsonify({
                    'success': True, 
                    'message': '实名认证成功！照片已保存，您现在可以使用人脸识别功能。'
                })
        finally:
            conn.close()
            
    except Exception as e:
        return jsonify({'success': False, 'message': f'认证过程中发生错误: {str(e)}'})
@app.route('/profile')
@login_required
def profile():
    """个人中心页面"""
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT name, username, major, class_name, student_id, 
                       is_verified, verify_time, profile_photo, phone, email
                FROM students WHERE id = %s
            """, (session['user_id'],))
            user_info = cursor.fetchone()
            
            if not user_info:
                flash('用户信息不存在', 'error')
                return redirect(url_for('dashboard'))
    finally:
        conn.close()
    
    return render_template('profile.html', user_info=user_info)

@app.route('/update_contact', methods=['POST'])
@login_required
def update_contact():
    """更新联系方式"""
    phone = request.form.get('phone', '').strip()
    email = request.form.get('email', '').strip()
    
    # 简单的邮箱格式验证
    if email and '@' not in email:
        flash('邮箱格式不正确', 'error')
        return redirect(url_for('profile'))
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                UPDATE students SET phone = %s, email = %s WHERE id = %s
            """, (phone if phone else None, email if email else None, session['user_id']))
            conn.commit()
            flash('联系方式更新成功', 'success')
    except Exception as e:
        flash('更新失败，请重试', 'error')
    finally:
        conn.close()
    
    return redirect(url_for('profile'))

@app.route('/face_recognition', methods=['POST'])
def face_recognition():
    """通用人脸识别接口，用于图书馆进入识别"""
    try:
        data = request.get_json()
        image_data = data.get('image')
        
        if not image_data:
            return jsonify({'success': False, 'message': '缺少图片数据'})
        
        # 解码base64图片
        image_base64 = image_data.split(',')[1] if ',' in image_data else image_data
        
        # 人脸检测
        face_detect_result = client.detect(image_base64, 'BASE64')
        
        if face_detect_result['error_code'] != 0:
            return jsonify({'success': False, 'message': '人脸检测失败'})
        
        if not face_detect_result['result']['face_list']:
            return jsonify({'success': False, 'message': '未检测到人脸，请确保面部清晰可见'})
        
        # 获取所有已认证用户的照片进行比对
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute("SELECT id, name, profile_photo FROM students WHERE profile_photo IS NOT NULL AND is_verified = 1")
                users = cursor.fetchall()
                
                best_match = None
                best_score = 0
                
                for user in users:
                    try:
                        # 人脸比对
                        compare_result = client.match([
                            {'image': image_base64, 'image_type': 'BASE64'},
                            {'image': user['profile_photo'], 'image_type': 'BASE64'}
                        ])
                        
                        if compare_result['error_code'] == 0:
                            score = compare_result['result']['score']
                            if score > best_score and score > 80:  # 相似度阈值
                                best_score = score
                                best_match = {'id': user['id'], 'name': user['name'], 'score': score}
                    except Exception as e:
                        continue  # 跳过比对失败的用户
                
                if best_match:
                    # 记录图书馆进入日志
                    cursor.execute(
                        "INSERT INTO library_entry_log (student_id, entry_time, recognition_score) VALUES (%s, %s, %s)",
                        (best_match['id'], datetime.now(), best_match['score'])
                    )
                    conn.commit()
                    
                    return jsonify({
                        'success': True, 
                        'message': f'欢迎来到图书馆，{best_match["name"]}！',
                        'user_name': best_match['name'],
                        'user_id': best_match['id'],
                        'similarity': best_match['score']
                    })
                else:
                    return jsonify({'success': False, 'message': '未找到匹配的用户，请先完成实名认证'})
        finally:
            conn.close()
        
    except Exception as e:
        return jsonify({'success': False, 'message': f'系统错误: {str(e)}'})

# 管理员人脸识别入馆API
@app.route('/admin/face_recognition/library_entry', methods=['POST'])
def admin_face_recognition_library_entry():
    try:
        data = request.get_json()
        if not data or 'image' not in data:
            return jsonify({'success': False, 'message': '请提供图片数据'})
        
        image_data = data['image']
        if not image_data.startswith('data:image/'):
            return jsonify({'success': False, 'message': '图片格式不正确'})
        
        # 提取base64数据
        base64_data = image_data.split(',')[1]
        
        # 使用AipFace客户端进行人脸检测
        face_detect_result = client.detect(base64_data, 'BASE64')
        
        if face_detect_result['error_code'] != 0:
            return jsonify({'success': False, 'message': '人脸检测失败，请重试'})
        
        if len(face_detect_result['result']['face_list']) == 0:
            return jsonify({'success': False, 'message': '未检测到人脸，请重新拍照'})
        
        face_list = face_detect_result.get('result', {}).get('face_list', [])
        if not face_list:
            return jsonify({'success': False, 'message': '未检测到人脸，请重新拍照'})
        
        # 使用本地数据库进行人脸比对
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                # 获取所有已认证用户的照片进行比对
                cursor.execute("SELECT id, name, profile_photo FROM students WHERE profile_photo IS NOT NULL AND is_verified = 1")
                users = cursor.fetchall()
                
                best_match = None
                best_score = 0
                
                for user in users:
                    try:
                        # 人脸比对
                        compare_result = client.match([
                            {'image': base64_data, 'image_type': 'BASE64'},
                            {'image': user['profile_photo'], 'image_type': 'BASE64'}
                        ])
                        
                        if compare_result['error_code'] == 0:
                            score = compare_result['result']['score']
                            if score > best_score and score > 80:  # 相似度阈值
                                best_score = score
                                best_match = {'id': user['id'], 'name': user['name'], 'score': score}
                    except Exception as e:
                        continue  # 跳过比对失败的用户
                
                if not best_match:
                    return jsonify({'success': False, 'message': '未找到匹配的学生信息，请确保已完成实名认证'})
                
                # 获取学生信息
                student_id = best_match['id']
        finally:
            conn.close()
        
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                # 检查学生是否存在
                cursor.execute("SELECT name FROM students WHERE id = %s", (student_id,))
                student = cursor.fetchone()
                if not student:
                    return jsonify({'success': False, 'message': '学生信息不存在'})
                
                # 检查学生是否有今日预约
                today = datetime.now().date()
                cursor.execute("""
                    SELECT id, seat_id FROM seat_reservations 
                    WHERE student_id = %s AND reservation_date = %s AND status = 'active'
                """, (student_id, today))
                existing_reservation = cursor.fetchone()
        
                if existing_reservation:
                    # 学生有预约，将预约状态改为使用中
                    cursor.execute("""
                        UPDATE seat_reservations 
                        SET status = '使用中', actual_start_time = %s 
                        WHERE id = %s
                    """, (datetime.now(), existing_reservation['id']))
                    
                    # 更新座位状态
                    cursor.execute("""
                        UPDATE library_seats 
                        SET status = 'occupied' 
                        WHERE id = %s
                    """, (existing_reservation['seat_id'],))
                    
                    # 获取座位信息
                    cursor.execute("""
                        SELECT seat_number, floor 
                        FROM library_seats 
                        WHERE id = %s
                    """, (existing_reservation['seat_id'],))
                    seat_info = cursor.fetchone()
                    
                    # 记录使用日志
                    cursor.execute("""
                        INSERT INTO seat_usage_logs (student_id, seat_id, start_time) 
                        VALUES (%s, %s, %s)
                    """, (student_id, existing_reservation['seat_id'], datetime.now()))
                    
                    conn.commit()
                    
                    return jsonify({
                        'success': True,
                        'message': f'欢迎来到图书馆，您的座位是{seat_info["floor"]}楼-{seat_info["seat_number"]}',
                        'student_name': student['name'],
                        'seat_info': {
                            'seat_number': seat_info['seat_number'],
                            'floor': seat_info['floor'],
                            'assignment_type': '预约座位激活'
                        }
                    })
                else:
                    # 学生没有预约，查找空闲座位
                    cursor.execute("""
                        SELECT id, seat_number, floor 
                        FROM library_seats 
                        WHERE status = 'available' 
                        LIMIT 1
                    """)
                    available_seat = cursor.fetchone()
                    
                    if not available_seat:
                        return jsonify({
                            'success': False,
                            'message': '对不起，目前图书馆无空闲位置'
                        })
                    
                    # 自动分配座位
                    cursor.execute("""
                        UPDATE library_seats 
                        SET status = 'occupied' 
                        WHERE id = %s
                    """, (available_seat['id'],))
                    
                    # 创建临时预约记录
                    cursor.execute("""
                        INSERT INTO seat_reservations 
                        (student_id, seat_id, reservation_date, start_time_slot, end_time_slot, actual_start_time, status) 
                        VALUES (%s, %s, %s, %s, %s, %s, %s)
                    """, (student_id, available_seat['id'], today, datetime.now().strftime('%H:%M'), '23:59', datetime.now(), '使用中'))
                    
                    # 记录使用日志
                    cursor.execute("""
                        INSERT INTO seat_usage_logs (student_id, seat_id, start_time) 
                        VALUES (%s, %s, %s)
                    """, (student_id, available_seat['id'], datetime.now()))
                    
                    conn.commit()
                    
                    return jsonify({
                        'success': True,
                        'message': f'欢迎来到图书馆，您的座位是{available_seat["floor"]}楼-{available_seat["seat_number"]}',
                        'student_name': student['name'],
                        'seat_info': {
                            'seat_number': available_seat['seat_number'],
                            'floor': available_seat['floor'],
                            'assignment_type': '现场自动分配'
                        }
                    })
        finally:
            conn.close()
            
    except Exception as e:
        print(f"管理员人脸识别入馆错误: {str(e)}")
        return jsonify({'success': False, 'message': '系统错误，请重试'})

# 管理员人脸识别页面路由
@app.route('/admin/face_recognition')
def admin_face_recognition_page():
    if 'user_id' not in session:
        return redirect(url_for('login'))
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT is_admin FROM students WHERE id = %s", (session['user_id'],))
            user = cursor.fetchone()
            if not user or not user['is_admin']:
                flash('权限不足', 'error')
                return redirect(url_for('dashboard'))
    finally:
        conn.close()
    
    return render_template('admin_face_recognition.html')

@app.route('/admin/face_recognition_exit')
@admin_required
def admin_face_recognition_exit():
    return render_template('admin_face_recognition_exit.html')

# 管理员人脸识别离馆API
@app.route('/admin/face_recognition/library_exit', methods=['POST'])
def admin_face_recognition_library_exit():
    try:
        data = request.get_json()
        if not data or 'image' not in data:
            return jsonify({'success': False, 'message': '请提供图片数据'})
        
        image_data = data['image']
        if not image_data.startswith('data:image/'):
            return jsonify({'success': False, 'message': '图片格式不正确'})
        
        # 提取base64数据
        base64_data = image_data.split(',')[1]
        
        # 使用AipFace客户端进行人脸检测
        face_detect_result = client.detect(base64_data, 'BASE64')
        
        if face_detect_result['error_code'] != 0:
            return jsonify({'success': False, 'message': '人脸检测失败，请重试'})
        
        if len(face_detect_result['result']['face_list']) == 0:
            return jsonify({'success': False, 'message': '未检测到人脸，请重新拍照'})
        
        face_list = face_detect_result.get('result', {}).get('face_list', [])
        if not face_list:
            return jsonify({'success': False, 'message': '未检测到人脸，请重新拍照'})
        
        # 使用本地数据库进行人脸比对
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                # 获取所有已认证用户的照片进行比对
                cursor.execute("SELECT id, name, profile_photo FROM students WHERE profile_photo IS NOT NULL AND is_verified = 1")
                users = cursor.fetchall()
                
                best_match = None
                best_score = 0
                
                for user in users:
                    try:
                        # 人脸比对
                        compare_result = client.match([
                            {'image': base64_data, 'image_type': 'BASE64'},
                            {'image': user['profile_photo'], 'image_type': 'BASE64'}
                        ])
                        
                        if compare_result['error_code'] == 0:
                            score = compare_result['result']['score']
                            if score > best_score and score > 80:  # 相似度阈值
                                best_score = score
                                best_match = {'id': user['id'], 'name': user['name'], 'score': score}
                    except Exception as e:
                        continue  # 跳过比对失败的用户
                
                if not best_match:
                    return jsonify({'success': False, 'message': '未找到匹配的学生信息'})
                
                # 获取学生信息
                student_id = best_match['id']
        finally:
            conn.close()
        
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                # 检查学生是否存在
                cursor.execute("SELECT name FROM students WHERE id = %s", (student_id,))
                student = cursor.fetchone()
                if not student:
                    return jsonify({'success': False, 'message': '学生信息不存在'})
                
                # 查找学生当前使用的座位
                today = datetime.now().date()
                cursor.execute("""
                    SELECT sr.id, sr.seat_id, ls.seat_number, ls.floor 
                    FROM seat_reservations sr
                    JOIN library_seats ls ON sr.seat_id = ls.id
                    WHERE sr.student_id = %s AND sr.reservation_date = %s AND sr.status IN ('已预约', '使用中', 'active', 'completed')
                """, (student_id, today))
                current_seat = cursor.fetchone()
                
                if not current_seat:
                    return jsonify({
                        'success': False,
                        'message': f'{student["name"]}同学，您今日没有使用中的座位记录'
                    })
                
                # 结束座位使用
                cursor.execute("""
                    UPDATE seat_reservations 
                    SET status = 'expired', actual_end_time = %s 
                    WHERE id = %s
                """, (datetime.now(), current_seat['id']))
                
                # 更新座位状态为可用
                cursor.execute("""
                    UPDATE library_seats 
                    SET status = 'available' 
                    WHERE id = %s
                """, (current_seat['seat_id'],))
                
                # 更新使用日志的结束时间
                cursor.execute("""
                    UPDATE seat_usage_logs 
                    SET end_time = %s 
                    WHERE student_id = %s AND seat_id = %s AND end_time IS NULL
                """, (datetime.now(), student_id, current_seat['seat_id']))
                
                conn.commit()
                
                return jsonify({
                    'success': True,
                    'message': f'再见，{student["name"]}同学！您已成功离开图书馆',
                    'student_name': student['name'],
                    'seat_info': {
                        'seat_number': current_seat['seat_number'],
                        'floor': current_seat['floor']
                    }
                })
        finally:
            conn.close()
            
    except Exception as e:
        print(f"管理员人脸识别离馆错误: {str(e)}")
        return jsonify({'success': False, 'message': '系统错误，请重试'})

# 座位使用日志通过直接SQL查询处理
# 由于使用pymysql直接操作数据库，不需要SQLAlchemy模型

# 添加全局模板变量
@app.context_processor
def inject_unread_messages():
    """为所有模板注入未读消息状态"""
    has_unread_messages = False
    if 'user_id' in session:
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute("""
                    SELECT COUNT(*) as count 
                    FROM lost_found_messages 
                    WHERE receiver_id = %s AND is_read = 0
                """, (session['user_id'],))
                result = cursor.fetchone()
                has_unread_messages = result['count'] > 0
        except:
            has_unread_messages = False
        finally:
            conn.close()
    
    return {'has_unread_messages': has_unread_messages}

# 勤工中心相关路由
@app.route('/job_center')
@login_required
def job_center():
    """勤工中心页面 - 根据用户角色跳转"""
    # 添加调试信息
    print(f"DEBUG: 用户ID: {session.get('user_id')}, 角色: {session.get('role')}, 是否管理员: {session.get('is_admin')}")
    
    if session.get('role') == 'admin':
        print("DEBUG: 跳转到管理员页面")
        return redirect(url_for('admin_job_management'))
    else:
        print("DEBUG: 显示普通用户页面")
        return render_template('job_center.html')

@app.route('/api/jobs')
@login_required
def get_jobs():
    """获取勤工岗位列表API"""
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT id, job_title, work_location, work_requirements, 
                       hourly_wage, job_description, contact_info, created_at
                FROM part_time_jobs
                WHERE status = '招聘中'
                ORDER BY created_at DESC
            """)
            jobs = cursor.fetchall()
            
            # 检查用户是否已投递简历
            user_id = session.get('user_id')
            for job in jobs:
                cursor.execute("""
                    SELECT COUNT(*) as count FROM job_applications 
                    WHERE job_id = %s AND student_id = %s
                """, (job['id'], user_id))
                result = cursor.fetchone()
                job['has_applied'] = result['count'] > 0
            
            return jsonify({'success': True, 'jobs': jobs})
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
    finally:
        conn.close()

@app.route('/api/apply_job', methods=['POST'])
@login_required
def apply_job():
    """简历上传API"""
    try:
        job_id = request.form.get('job_id')
        if 'resume' not in request.files:
            return jsonify({'success': False, 'error': '请选择简历文件'})
        
        file = request.files['resume']
        if file.filename == '':
            return jsonify({'success': False, 'error': '请选择简历文件'})
        
        # 检查文件类型
        if not file.filename.lower().endswith(('.doc', '.docx')):
            return jsonify({'success': False, 'error': '只支持Word文档格式(.doc, .docx)'})
        
        # 创建上传目录
        upload_dir = os.path.join('uploads', 'resumes')
        os.makedirs(upload_dir, exist_ok=True)
        
        # 生成唯一文件名
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        filename = f"{session['user_id']}_{job_id}_{timestamp}_{file.filename}"
        file_path = os.path.join(upload_dir, filename)
        
        # 保存文件
        file.save(file_path)
        
        # 保存到数据库
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                # 检查是否已经投递过
                cursor.execute("""
                    SELECT id FROM job_applications 
                    WHERE job_id = %s AND student_id = %s
                """, (job_id, session['user_id']))
                
                if cursor.fetchone():
                    # 更新现有记录
                    cursor.execute("""
                        UPDATE job_applications 
                        SET resume_filename = %s, resume_path = %s, application_time = %s
                        WHERE job_id = %s AND student_id = %s
                    """, (file.filename, file_path, datetime.now(), job_id, session['user_id']))
                else:
                    # 插入新记录
                    cursor.execute("""
                        INSERT INTO job_applications (job_id, student_id, resume_filename, resume_path)
                        VALUES (%s, %s, %s, %s)
                    """, (job_id, session['user_id'], file.filename, file_path))
                
                conn.commit()
                return jsonify({'success': True, 'message': '简历投递成功'})
        finally:
            conn.close()
            
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})

# 管理员端勤工岗位管理相关路由
@app.route('/admin/job_management')
@admin_required
def admin_job_management():
    """管理员端勤工岗位管理页面"""
    return render_template('admin_job_management.html')

@app.route('/api/admin/post_job', methods=['POST'])
@admin_required
def admin_post_job():
    """管理员发布岗位API"""
    try:
        data = request.get_json()
        title = data.get('title')
        location = data.get('location')
        requirements = data.get('requirements')
        hourly_rate = data.get('hourly_rate')
        description = data.get('description')
        
        if not all([title, location, requirements, hourly_rate]):
            return jsonify({'success': False, 'error': '请填写所有必填字段'})
        
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute("""
                    INSERT INTO part_time_jobs (job_title, work_location, work_requirements, hourly_wage, job_description, publisher_id)
                    VALUES (%s, %s, %s, %s, %s, %s)
                """, (title, location, requirements, hourly_rate, description, session['user_id']))
                conn.commit()
                return jsonify({'success': True, 'message': '岗位发布成功'})
        finally:
            conn.close()
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})

@app.route('/api/admin/jobs')
@admin_required
def admin_get_jobs():
    """管理员获取岗位列表API"""
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT ptj.id, ptj.job_title as title, ptj.work_location as location, 
                       ptj.work_requirements as requirements, ptj.hourly_wage as hourly_rate, 
                       ptj.job_description as description, ptj.created_at as post_time, ptj.status,
                       COUNT(ja.id) as application_count
                FROM part_time_jobs ptj
                LEFT JOIN job_applications ja ON ptj.id = ja.job_id
                GROUP BY ptj.id
                ORDER BY ptj.created_at DESC
            """)
            jobs = cursor.fetchall()
            return jsonify({'success': True, 'jobs': jobs})
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
    finally:
        conn.close()

@app.route('/api/admin/job_applications/<int:job_id>')
@admin_required
def admin_get_job_applications(job_id):
    """管理员查看岗位简历API"""
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT ja.id, ja.application_time, ja.resume_path, ja.resume_filename, s.name as student_name, s.student_id, s.major, s.class_name
                FROM job_applications ja
                JOIN students s ON ja.student_id = s.id
                WHERE ja.job_id = %s
                ORDER BY ja.application_time DESC
            """, (job_id,))
            applications = cursor.fetchall()
            return jsonify({'success': True, 'applications': applications})
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
    finally:
        conn.close()

@app.route('/admin/update_seat_status', methods=['POST'])
@admin_required
def admin_update_seat_status():
    """管理员修改座位状态API"""
    try:
        data = request.get_json()
        seat_id = data.get('seat_id')
        new_status = data.get('status')
        student_id = data.get('student_id')
        
        if not seat_id or not new_status:
            return jsonify({'success': False, 'error': '缺少必要参数'})
        
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                today = datetime.now().date()
                
                # 删除该座位今日的现有预约记录
                cursor.execute("""
                    DELETE FROM seat_reservations 
                    WHERE seat_id = %s AND reservation_date = %s
                """, (seat_id, today))
                
                # 根据新状态创建相应记录
                if new_status == 'reserved':
                    if not student_id:
                        return jsonify({'success': False, 'error': '预约状态需要提供学生ID'})
                    
                    # 查找学生ID对应的数据库ID
                    cursor.execute("SELECT id FROM students WHERE student_id = %s", (student_id,))
                    student = cursor.fetchone()
                    if not student:
                        return jsonify({'success': False, 'error': '学生ID不存在'})
                    
                    # 创建预约记录
                    cursor.execute("""
                        INSERT INTO seat_reservations (seat_id, student_id, reservation_date, start_time_slot, end_time_slot, status)
                        VALUES (%s, %s, %s, '08:00', '18:00', 'active')
                    """, (seat_id, student['id'], today))
                    
                elif new_status == 'occupied':
                    if not student_id:
                        return jsonify({'success': False, 'error': '使用中状态需要提供学生ID'})
                    
                    # 查找学生ID对应的数据库ID
                    cursor.execute("SELECT id FROM students WHERE student_id = %s", (student_id,))
                    student = cursor.fetchone()
                    if not student:
                        return jsonify({'success': False, 'error': '学生ID不存在'})
                    
                    # 创建使用中记录
                    cursor.execute("""
                        INSERT INTO seat_reservations (seat_id, student_id, reservation_date, start_time_slot, end_time_slot, status, actual_start_time)
                        VALUES (%s, %s, %s, '08:00', '18:00', 'completed', NOW())
                    """, (seat_id, student['id'], today))
                
                # 更新座位表状态
                cursor.execute("""
                    UPDATE library_seats SET status = %s WHERE id = %s
                """, (new_status, seat_id))
                
                conn.commit()
                return jsonify({'success': True, 'message': '座位状态更新成功'})
                
        finally:
            conn.close()
            
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})

@app.route('/api/admin/all_applications')
@admin_required
def admin_get_all_applications():
    """管理员查看所有简历API"""
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT ja.id, ja.application_time, ja.resume_path, ja.resume_filename, 
                       s.name as student_name, s.student_id, s.major, s.class_name,
                       ptj.job_title, ptj.work_location, ptj.hourly_wage
                FROM job_applications ja
                JOIN students s ON ja.student_id = s.id
                JOIN part_time_jobs ptj ON ja.job_id = ptj.id
                ORDER BY ja.application_time DESC
            """)
            applications = cursor.fetchall()
            return jsonify({'success': True, 'applications': applications})
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
    finally:
        conn.close()

@app.route('/api/admin/download_resume/<int:application_id>')
@admin_required
def admin_download_resume(application_id):
    """管理员下载简历API"""
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT ja.resume_path, s.name
                FROM job_applications ja
                JOIN students s ON ja.student_id = s.id
                WHERE ja.id = %s
            """, (application_id,))
            result = cursor.fetchone()
            
            if not result:
                return jsonify({'success': False, 'error': '简历不存在'})
            
            file_path = result['resume_path']
            student_name = result['name']
            
            if os.path.exists(file_path):
                from flask import send_file
                return send_file(file_path, as_attachment=True, 
                               download_name=f"{student_name}_简历.{file_path.split('.')[-1]}")
            else:
                return jsonify({'success': False, 'error': '文件不存在'})
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
    finally:
        conn.close()

@app.route('/api/admin/preview_resume/<int:application_id>')
@admin_required
def admin_preview_resume(application_id):
    """管理员快速预览简历API - 返回HTML格式的预览"""
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT ja.resume_path, s.name, ja.application_time
                FROM job_applications ja
                JOIN students s ON ja.student_id = s.id
                WHERE ja.id = %s
            """, (application_id,))
            result = cursor.fetchone()
            
            if not result:
                return jsonify({'success': False, 'error': '简历不存在'})
            
            file_path = result['resume_path']
            student_name = result['name']
            application_time = result['application_time']
            
            if os.path.exists(file_path):
                try:
                    # 尝试读取Word文档内容
                    import docx
                    doc = docx.Document(file_path)
                    content = []
                    for paragraph in doc.paragraphs:
                        if paragraph.text.strip():
                            content.append(paragraph.text)
                    
                    # 生成HTML预览页面
                    html_content = f"""
                    <!DOCTYPE html>
                    <html>
                    <head>
                        <meta charset="UTF-8">
                        <title>简历预览 - {student_name}</title>
                        <style>
                            body {{ font-family: Arial, sans-serif; margin: 20px; line-height: 1.6; }}
                            .header {{ background: #f4f4f4; padding: 15px; border-radius: 5px; margin-bottom: 20px; }}
                            .content {{ background: white; padding: 20px; border: 1px solid #ddd; border-radius: 5px; }}
                            .download-btn {{ background: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block; margin-top: 15px; }}
                            .download-btn:hover {{ background: #0056b3; }}
                            .paragraph {{ margin-bottom: 10px; }}
                        </style>
                    </head>
                    <body>
                        <div class="header">
                            <h2>简历预览</h2>
                            <p><strong>学生姓名：</strong>{student_name}</p>
                            <p><strong>申请时间：</strong>{application_time}</p>
                            <a href="/api/admin/download_resume/{application_id}" class="download-btn">下载完整简历</a>
                        </div>
                        <div class="content">
                            <h3>简历内容：</h3>
                    """
                    
                    for para in content:
                        html_content += f'<div class="paragraph">{para}</div>'
                    
                    html_content += """
                        </div>
                    </body>
                    </html>
                    """
                    
                    return html_content
                    
                except Exception as doc_error:
                    # 如果无法读取Word文档，返回基本信息和下载链接
                    html_content = f"""
                    <!DOCTYPE html>
                    <html>
                    <head>
                        <meta charset="UTF-8">
                        <title>简历预览 - {student_name}</title>
                        <style>
                            body {{ font-family: Arial, sans-serif; margin: 20px; line-height: 1.6; }}
                            .header {{ background: #f4f4f4; padding: 15px; border-radius: 5px; margin-bottom: 20px; }}
                            .content {{ background: white; padding: 20px; border: 1px solid #ddd; border-radius: 5px; text-align: center; }}
                            .download-btn {{ background: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block; margin: 15px; }}
                            .download-btn:hover {{ background: #0056b3; }}
                            .error {{ color: #dc3545; margin-bottom: 20px; }}
                        </style>
                    </head>
                    <body>
                        <div class="header">
                            <h2>简历预览</h2>
                            <p><strong>学生姓名：</strong>{student_name}</p>
                            <p><strong>申请时间：</strong>{application_time}</p>
                        </div>
                        <div class="content">
                            <div class="error">无法预览此文档格式，请下载查看完整内容</div>
                            <a href="/api/admin/download_resume/{application_id}" class="download-btn">下载完整简历</a>
                        </div>
                    </body>
                    </html>
                    """
                    return html_content
            else:
                return jsonify({'success': False, 'error': '文件不存在'})
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
    finally:
        conn.close()

@app.route('/api/admin/delete_job/<int:job_id>', methods=['DELETE'])
@admin_required
def admin_delete_job(job_id):
    """管理员删除岗位API"""
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 先删除相关的简历申请记录
            cursor.execute("DELETE FROM job_applications WHERE job_id = %s", (job_id,))
            # 再删除岗位
            cursor.execute("DELETE FROM part_time_jobs WHERE id = %s", (job_id,))
            conn.commit()
            
            if cursor.rowcount > 0:
                return jsonify({'success': True, 'message': '岗位删除成功'})
            else:
                return jsonify({'success': False, 'error': '岗位不存在'})
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
    finally:
        conn.close()

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5000)
